package com.xm.dao;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.servlet.annotation.WebServlet;

import com.mysql.jdbc.StringUtils;
import com.xm.entity.Message;
import com.xm.uti.DBManager;
import com.xm.uti.QueryDataResult;

/**
 * Servlet implementation class MessageDaoImpl
 */
@WebServlet("/MessageDaoImpl")
public class MessageDaoImpl {
	/**
	 * 留言板页面
	 * 
	 * @param m
	 * @return
	 */
	public boolean insertMessage(Message m) {
		String sql = "insert into messages (name,tel,email,content) values('" + m.getName() + "','" + m.getTel()
		+ "','" + m.getEmail() + "','" + m.getContent() + "')";
		System.out.println("执行的sql语句是" + sql);
		int i = DBManager.executeUpdate(sql);
		if (i > 0) {
			return true;
		}
		return false;
	}
	/**
	 * 分页查询
	 * @param name
	 * @param state
	 * @param mindate
	 * @param maxdate
	 * @param page
	 * @param limit
	 * @return
	 */
	public ArrayList<Message> getmessage(String name,String state,String mindate,String maxdate,String page,String limit) {
		String sql = "select * from messages where 1=1 ";
		if(!StringUtils.isNullOrEmpty(name)) {
			sql = sql + " and name like '%"+name+"%'";
		}
		if(!StringUtils.isNullOrEmpty(state)) {
			sql = sql + " and state = '"+state+"' " ;
		}
		if(!StringUtils.isNullOrEmpty(mindate)) {
			sql = sql + " and submitTime >= '"+mindate+"'";
		}
		if(!StringUtils.isNullOrEmpty(maxdate)) {
			sql = sql + " and submitTime <= '"+maxdate+"'";
		}
		sql = sql + " order by submitTime desc";
		if(!StringUtils.isNullOrEmpty(page) && !StringUtils.isNullOrEmpty(limit)) {
			int p = Integer.valueOf(page);
			int c = Integer.valueOf(limit);
			int start = (p-1) * c;
			sql = sql + " limit "+start + ", "+c;
		}
		System.out.println(sql);
		QueryDataResult executeQuery = DBManager.executeQuery(sql);
		ArrayList<Message> list = new ArrayList<Message>();
		if (executeQuery != null) {
			ResultSet rs = executeQuery.rs;
			try {
				while (rs.next()) {
					Message e = new Message();
					e.setId(rs.getInt("id"));
					e.setName(rs.getString("name"));
					e.setTel(rs.getString("tel"));
					e.setEmail(rs.getString("email"));
					e.setContent(rs.getString("content"));
					e.setSubmitTime(rs.getDate("submitTime"));
					e.setState(rs.getString("state"));
					e.setReplytime(rs.getDate("replyTime"));
					e.setRemark(rs.getString("remark"));
					list.add(e);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				executeQuery.close();
			}
		}
		System.out.println(list);
		return list;
	}
	/**
	 * 全查
	 * 
	 * @return
	 */
	public ArrayList<Message> getAllMessage() {
		String sql = "select * from messages where 1=1 ";
		System.out.println("执行的sql语句是" + sql);
		QueryDataResult exeQuery = DBManager.executeQuery(sql);
		ArrayList<Message> list = new ArrayList<Message>();
		if (exeQuery != null) {
			ResultSet rs = exeQuery.rs;
			try {
				while (rs.next()) {
					Message m = new Message(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),
							rs.getString(5), rs.getDate(6), rs.getString(7), rs.getDate(8), rs.getString(9));
					list.add(m);

				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				exeQuery.close();
			}
		}
		System.out.println(list);
		return list;

	}


	/**
	 * 修改
	 * 
	 * @param m
	 * @return
	 */
	public boolean updateMessage(Message m) {
		String sql = "update messages set state = '" + m.getState() + "' , replyTime = now() ,remark = '"
				+ m.getRemark() + "' where id = " + m.getId();
		System.out.println("执行的sql语句是" + sql);
		int i = DBManager.executeUpdate(sql);
		if (i > 0) {
			return true;
		}
		return false;
	}
	
	/**
	 * 删除
	 * @param m
	 * @return
	 */
	public boolean delmessage(int mid) {
		String sql = "delete from messages where id=" + mid;
		int i = DBManager.executeUpdate(sql);
		if (i > 0) {
			return true;
		}
		return false;
	}

}